
/**
	DESCRIPTION	: THIS PROCEDURE IS USED TO GET THE PART NUMBER PRICE ENTERED IN THE PRICE SETUP WITH RESPECT TO THE ORDER TYPE i.e SAMPLE OR REGULAR. 
			  EXISTANCE OF THE PRICE IS TESTED IN THE ORDER CUSTOMER PRICING, QUARTER PRICING AND STANDARD PRICING 
	USAGE           : EXEC SP_LOAD_LINEITEM_PRICE 750, 423 , 15, '04/03/06', 0, 1  
*/	

ALTER PROC SP_LOAD_LINEITEM_PRICE

	@PRODUCT_ID 	 INT,
	@CUSTOMER_ID     INT,
	@QUANTITY   	 INT,
	@REQUESTED_DATE  DATETIME,
	@PRICE		 FLOAT    OUTPUT,
	@IS_SAMPLE	INT	

AS

BEGIN
	DECLARE @DB_PRICE FLOAT 
	SELECT @DB_PRICE = CPT_UNIT_PRICE FROM CUSTOMER_PRICING_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND CUSTOMER_ID = @CUSTOMER_ID AND ( @QUANTITY BETWEEN CPT_QTY_FROM AND CPT_QTY_TO ) AND IS_SAMPLE = @IS_SAMPLE

	IF ( @DB_PRICE IS NULL  )
	BEGIN
	SELECT @DB_PRICE = QPT_UNIT_PRICE FROM QUARTER_PRICING_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND ( @QUANTITY BETWEEN QPT_QTY_FROM AND QPT_QTY_TO ) AND QUARTER_ID = ( SELECT CQC_QUARTER_ID FROM COMPANY_QUARTERLY_CALENDAR WHERE ( @REQUESTED_DATE BETWEEN CQC_START_DATE AND CQC_END_DATE ) ) AND IS_SAMPLE = @IS_SAMPLE
	END
	IF ( @DB_PRICE IS NULL  )
	BEGIN
		SELECT @DB_PRICE = PPT_UNIT_PRICE FROM PRODUCT_PRICE_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND ( @QUANTITY BETWEEN PPT_QTY_FROM AND PPT_QTY_TO ) AND IS_SAMPLE = @IS_SAMPLE
	END
	IF ( @DB_PRICE IS NULL )
	BEGIN
		SELECT @DB_PRICE = PPT.PPT_UNIT_PRICE FROM PRODUCT P, PRODUCT_PRICE_TABLE PPT WHERE P.PRODUCT_ID = @PRODUCT_ID AND  P.P_TYPE IN( 'BOARD','KIT' ) AND P.PRODUCT_ID = PPT.PRODUCT_ID AND ( ( PPT_QTY_FROM = 1 AND PPT_QTY_TO = 1 ) OR ( PPT_QTY_FROM IS NULL AND PPT_QTY_TO IS NULL ) ) AND PPT.IS_SAMPLE = @IS_SAMPLE
	END	
	SET @PRICE = @DB_PRICE
	
	SELECT @PRICE
	RETURN
END